﻿using System;
using System.Collections.Generic;
using KPIS.DBM;
using System.Configuration;
using KPIS.GERP.ESS.MODEL;
using System.Data;

namespace KPIS.GERP.ESS.DAL
{
    public class D_ACM_GENERATE_ACCOUNT_Auto
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        private ConvertNullable.ConvertNullable cn = new ConvertNullable.ConvertNullable();

        public IList<M_ACM_GENERATE_ACCOUNT_Auto> GetListMaster(string bgm_source_seq, string work_area_seq, string ref_type)
        {
            Nullable<int> nbgm_source_seq = cn.ConvertNullInt(bgm_source_seq);
            Nullable<int> nwork_area_seq = cn.ConvertNullInt(work_area_seq);
            //Nullable<int> nwork_area_seq = 1;
            DataTable drc = null;
            IList<M_ACM_GENERATE_ACCOUNT_Auto> infoList = new List<M_ACM_GENERATE_ACCOUNT_Auto>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                //dbManager.AddParameters(0, "@ref_type", info.ref_type);
                dbManager.AddParameters(0, "@bgm_source_seq", nbgm_source_seq);
                dbManager.AddParameters(1, "@work_area_seq", 1);
                dbManager.AddParameters(2, "@DeleteStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@ApproveStatus", SystemInfo._ApproveRecordStatus);

                string strSQL =
                #region petition
                    // petition จ่ายผ่านส่วนราชการ =========================================================================
                    "SELECT * FROM"
                    + " (SELECT 'fim_petitions' as ref_type"
                    + ", 'G' as tran_type"
                    + ", pt.ref_no"
                    + ", pt.petition_date as tran_date"
                    + ", concat('รายการขอเบิกเลขที่ ', coalesce(pt.ref_no, '')) as tran_name"
                    + ", 1 as work_area_seq"
                    + ", pt.bgm_source_seq"
                    + ", pt.petition_seq as ref_seq"
                    + ", pt.bgm_year"
                    + ", null as year_no, null as month_no"

                    + " FROM fim_petitions pt"
                    + " WHERE pt.record_status = @ApproveStatus"
                    + " AND pt.tran_grp_seq is null"
                    + " AND pt.direct_pay = '0'"

                    // petition จ่ายตรง =========================================================================
                    + " union all "

                    + " SELECT 'fim_petitions' as ref_type"
                    + ", 'G' as tran_type"
                    + ", pt.ref_no"
                    + ", pt.petition_date as tran_date"
                    + ", concat('รายการขอเบิกเลขที่ ', coalesce(pt.ref_no, '')) as tran_name"
                    + ", 1 as work_area_seq"
                    + ", pt.bgm_source_seq"
                    + ", pt.petition_seq as ref_seq"
                    + ", pt.bgm_year"
                    + ", null as year_no, null as month_no"
                    //+ " , concat(coalesce(bgm_source_code, ''), ' : ', coalesce(bgm_source_name, ''))  as source_name "
                    //+ " , concat(coalesce(work_area_id, ''), ' : ', coalesce(work_area_name, ''))  as area_name "
                    //+ " , '2' as dtls_type"
                    + " FROM fim_petitions pt "
                    + " LEFT JOIN bgm_mas_source bgm_source ON "
                        + " pt.bgm_source_seq = bgm_source.bgm_source_seq "
                    + " LEFT JOIN hrm_mp_work_areas work_areas ON "
                        + " 1 = work_areas.work_area_seq "
                    + " WHERE pt.record_status = 'A' "
                    + " AND pt.tran_grp_seq is null  AND pt.direct_pay = '1' "
                #endregion
                #region money_request
                    // money_request =========================================================================
                    + " union all"

                    + " SELECT 'fim_money_requests' as ref_type"
                    + ", 'G' as tran_type"
                    + ", mr.ref_no"
                    + ", mr.money_req_date as tran_date"
                    + ", mr.money_req_name as tran_name"
                    + ", 1 as work_area_seq"
                    + ", mr.bgm_source_seq"
                    + ", mr.money_req_seq as ref_seq"
                    + ", mr.bgm_year"
                    + ", null as year_no, null as month_no"

                    + " FROM fim_money_requests mr"

                    + " WHERE mr.record_status = 'A'"
                    + " AND mr.tran_grp_seq is null"
                    + " AND mr.event_seq not in (16, 18)"
                    + " AND mr.use_advance <> 1"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND mr.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND mr.work_area_seq = @work_area_seq" : "")
                #endregion
                #region fim_payments
                    // payment =========================================================================
                    + " union all "// จ่ายเงินสด และโอน

                    + " SELECT 'fim_payments' as ref_type"
                    + ", 'P' as tran_type"
                    + ", pay.ref_no"
                    + ", pay.real_pay_date as tran_date"
                    + ", pay.pay_name as tran_name"
                    + ", 1 as work_area_seq"
                    + ", pay.bgm_source_seq"
                    + ", pay.pay_seq as ref_seq"
                    + ", pay.bgm_year"
                    + ", null as year_no, null as month_no"

                    + " FROM fim_payments pay"
                    + " left join fim_money_requests mr on pay.money_req_seq = mr.money_req_seq and mr.record_status = 'A'"
                    + " WHERE pay.record_status = 'A'"
                    + " AND pay.tran_grp_seq is null"
                    //+ " AND (pet.direct_pay is null OR pet.direct_pay = '0')"
                    + " AND pay.method_seq in (1, 3)"
                    + " AND pay.real_pay_date is not null"
                    + " AND pay.event_seq <> 19"
                    + " AND coalesce(mr.use_advance, 0) <> 1"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND pay.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND pay.work_area_seq = @work_area_seq" : "")

                    + " union all "// จ่ายเช็ค

                    + @"
SELECT 'fim_payments' as ref_type
, 'P' as tran_type
, pay.ref_no
, cheq.pay_date as tran_date
, pay.pay_name + '[ เช็คเลขที่ '+ c.cheque_no + ' ]' as tran_name
, 1 as work_area_seq
, pay.bgm_source_seq
, pay.pay_seq as ref_seq
, pay.bgm_year
, null as year_no, null as month_no
FROM fim_payments pay
left join (
	select pc.pay_seq, STUFF((
		SELECT N', ' + CAST(c.cheque_no AS VARCHAR(255))
		from fim_payment_cheques f1
		left join fim_cheques c on f1.cheque_seq = c.cheque_seq
		WHERE pc.pay_seq = f1.pay_seq
			and pc.record_status <> 'D'
			and c.record_status <> 'D'
			and c.cheque_status <> '0'
		FOR XML PATH ('')), 1, 2, '') AS cheque_no
	from fim_payment_cheques pc
	where pc.record_status <> 'D'

	GROUP BY pc.pay_seq, pc.record_status
) c on c.pay_seq = pay.pay_seq
left join fim_payment_cheques pc on pay.pay_seq = pc.pay_seq
left join fim_cheques cheq on pc.cheque_seq = cheq.cheque_seq
left join fim_money_requests mr on pay.money_req_seq = mr.money_req_seq and mr.record_status = 'A'

WHERE pay.record_status = 'A'
AND pay.tran_grp_seq is null
AND pay.method_seq in (2)
AND cheq.pay_date is not null
AND pay.event_seq <> 19
AND coalesce(mr.use_advance, 0) <> 1
"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND pay.bgm_source_seq = @bgm_source_seq" : "")

                    + " union all "// จ่าย pay in pack

                    + @"
SELECT 'fim_payments' as ref_type
, 'P' as tran_type
, pay.ref_no
, pip.pip_date as tran_date
, pay.pay_name + ' [pip : ' + pip.pip_no + ']' as tran_name
, 1 as work_area_seq
, pay.bgm_source_seq
, pay.pay_seq as ref_seq
, pay.bgm_year
, null as year_no, null as month_no
FROM fim_payments pay
left join fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq
    and pip.record_status <> 'D'
left join fim_money_requests mr on pay.money_req_seq = mr.money_req_seq and mr.record_status = 'A'
WHERE pay.record_status = 'A'
AND pay.tran_grp_seq is null
AND pay.method_seq in (4)
AND pip.pip_date is not null
AND pay.event_seq <> 19
AND coalesce(mr.use_advance, 0) <> 1
"
                    //+ " AND pay.method_seq = 2"
                    //+ " AND c.pay_date is not null"
                    //+ " AND c.cheque_status = 2"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND pay.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND pay.work_area_seq = @work_area_seq" : "")
                #endregion
                #region fim_receives
                    // receive =========================================================================
                    + " union all "

                    + " SELECT 'fim_receives' as ref_type"
                    + ", 'R' as tran_type"
                    + ", r.ref_no"
                    + ", r.receive_date as tran_date"
                    + ", case receive_mode when 1 then concat('รับเงินเบิกเกินส่งคืน ใบเสร็จเลขที่ ', coalesce(r.receipt_no, ''))"
                        + " when 2 then concat('รายการรับเงิน ใบเสร็จเลขที่ ', coalesce(r.receipt_no, ''))"
                        + " when 3 then concat('รับเงินจากรายการขอเบิก เลขที่ ', coalesce(pet.petition_no, ''))"
                        + " end as tran_name"
                    + ", 1 as work_area_seq"
                    + ", r.bgm_source_seq"
                    + ", r.receive_seq as ref_seq"
                    + ", r.bgm_year"
                    + ", null as year_no, null as month_no"

                    + " FROM fim_receives r"
                    + " LEFT JOIN fim_petitions pet on r.petition_seq = pet.petition_seq"
                    + " LEFT JOIN fim_money_requests mr on r.money_req_seq = mr.money_req_seq"
                    + " WHERE r.record_status not in ('D', 'C')"
                    + " and (COALESCE(r.receipt_no, '') not like '' or receive_mode = '3')"
                    + " AND r.tran_grp_seq is null"
                    + " AND coalesce(mr.use_advance, 0) <> 1"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND r.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND r.work_area_seq = @work_area_seq" : "")
                #endregion
                #region fim_money_returns
                    // return =========================================================================
                    + " union all "

                    + " SELECT 'fim_money_returns' as ref_type"
                    + ", 'G' as tran_type"
                    + ", ret.ref_no"
                    + ", ret.return_date as tran_date"
                    + ", concat('รายการหักล้าง ', coalesce(mr.money_req_name, '')) as tran_name"
                    + ", mr.work_area_seq"
                    + ", mr.bgm_source_seq"
                    + ", ret.return_seq as ref_seq"
                    + ", mr.bgm_year"
                    + ", null as year_no, null as month_no"

                    + " FROM fim_money_returns ret"
                    + " Left Join fim_money_requests AS mr ON ret.money_req_seq = mr.money_req_seq"
                    + " WHERE ret.record_status = 'A'"
                    + " AND ret.tran_grp_seq is null"
                    + " AND coalesce(mr.use_advance, 0) <> 1"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND mr.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND mr.work_area_seq = @work_area_seq" : "")
                #endregion
                #region icm_delivery_note
                    //// delivery_note =========================================================================
                    + " union all "

                    + " SELECT distinct 'icm_delivery_note' as ref_type"
                    + ", 'G' as tran_type"
                    + ", dn.Code as ref_no"
                    + ", dn.ReceiveDate as tran_date"
                    + ", dn.Name as tran_name"
                    + ", 1 as work_area_seq"
                    + ", ppri.BgSourceId as bgm_source_seq"
                    + ", dn.DnId as ref_seq"
                    + ", ppr.BgYear as bgm_year"
                    + ", null as year_no, null as month_no"
                    + " FROM IcmDeliveryNote dn"
                    + " left join IcmPurchaseOrderInstallmentItem poii on dn.PoInstallId = poii.PoInstallId"
                    + " left join IcmBidderQuotation quo on poii.BidderQuoId = quo.BidderQuoId"
                    //+ " left join IcmPurchaseRequestItem pri on quo.PrItemId = pri.PrItemId"
                    + " left join IcmPrePurchaseRequestItem ppri on quo.PrItemId = ppri.PrItemId"
                    + " left join IcmPrePurchaseRequest ppr on ppri.PprId = ppr.PprId"
                    //+ " LEFT JOIN bgm_project_operation_expense ope on ppri.ProjOperExpId = ope.bgm_proj_oper_exp_seq"
                    //+ " LEFT JOIN bgm_project_expense pe on ope.bgm_proj_exp_seq = pe.bgm_proj_exp_seq"
                    //+ " LEFT JOIN bgm_mas_source bgm_source ON 1 = bgm_source.bgm_source_seq "
                    //+ " LEFT JOIN hrm_mp_work_areas work_areas ON 1 = work_areas.work_area_seq "
                    + " WHERE dn.RecordStatus = 'A'"
                    + " AND dn.TranGrpId is null"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND ppri.BgSourceId = @bgm_source_seq" : "")
                    //+ (!string.IsNullOrEmpty(work_area_seq) ? " AND mr.work_area_seq = @work_area_seq" : "")
                #endregion
                #region fim_transfers
                    // transfer =========================================================================
                    + " union all "

                    + " SELECT 'fim_transfers' as ref_type"
                    + ", 'G' as tran_type"
                    + ", t.ref_no"
                    + ", t.transfer_date as tran_date"
                    + ", concat(' ', coalesce(t.transfer_name, '')) as tran_name"
                    + ", 1 as work_area_seq"
                    + ", t.bgm_source_seq as bgm_source_seq"
                    + ", t.transfer_seq as ref_seq"
                    + ", t.bgm_year"
                    + ", null as year_no, null as month_no"

                    + " FROM fim_transfers t"
                    + " WHERE t.record_status not like 'D'"
                    + " AND t.tran_grp_seq is null"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND t.bgm_source_seq = @bgm_source_seq" : "")
                #endregion
                #region fim_cheques
 + " union all "

                    + " SELECT distinct 'fim_cheques' as ref_type"
                    + ", 'G' as tran_type"
                    + ", concat('CH', c.cheque_no) as ref_no"
                    + ", c.actual_pay_date as tran_date"
                    + ", concat('auto ', c.cheque_no) as tran_name"
                    + ", 1 as work_area_seq"
                    + ", bacc.bgm_source_seq as bgm_source_seq"
                    + ", c.cheque_seq as ref_seq"
                    + ", p.bgm_year"
                    + ", null as year_no, null as month_no"

                    + " FROM fim_cheques c"
                    + " inner join fim_payment_cheques pc on c.cheque_seq = pc.cheque_seq"
                        + " and pc.record_status not like 'D'"
                    + " inner join fim_payments p on pc.pay_seq = p.pay_seq"
                        + " and p.record_status = 'A'"
                    + " inner join fim_mas_bank_accounts bacc on c.bank_acc_seq = bacc.bank_acc_seq"
                    + " inner join fim_mas_bank_accounts bacc1 on bacc.saving_bank_acc_seq = bacc1.bank_acc_seq"//ต้องผูกบัญชีออมทรัพย์แล้ว
                    + " WHERE c.record_status not like 'D'"
                    + " and c.cheque_status = 3"
                    + " AND c.tran_grp_seq is null"
                    + " AND c.actual_pay_date is not null"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND bacc.bgm_source_seq = @bgm_source_seq" : "")
                #endregion
                #region fim_vat
 + @"
 union all

SELECT 'fim_vat' as ref_type
, 'G' as tran_type
, concat('V-', v.year_no, '-', v.month_no) as ref_no
, case when v.pay_seq is null
    then null
    else p.pay_date
    end as tran_date
, concat('คำนวนภาษี vat V-', v.year_no, '-', v.month_no) as tran_name
, 1 as work_area_seq
, v.bgm_source_seq as bgm_source_seq
, v.vat_seq as ref_seq
, v.bgm_year
, v.year_no, v.month_no
from fim_vat v
left join fim_payments p on v.pay_seq = p.pay_seq
where v.record_status <> 'D'
and v.tran_grp_seq is null"
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " AND v.bgm_source_seq = @bgm_source_seq" : "")
                #endregion
 + ") t"
                    + " where 0 = 0"
                    + (!string.IsNullOrEmpty(ref_type) ? " and t.ref_type = '" + ref_type + "'" : "")
                    + " order by tran_date";
                //, dateadd(day, -1,dateadd(month, 1, concat(v.year_no - 543, '-', v.month_no, '-', 1))) as tran_date

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (drc.Rows.Count > 0)
                {
                    for (int i = 0; i < drc.Rows.Count; i++)
                    {
                        M_ACM_GENERATE_ACCOUNT_Auto info = new M_ACM_GENERATE_ACCOUNT_Auto();
                        info.ref_type = drc.Rows[i]["ref_type"].ToString();
                        info.tran_type = drc.Rows[i]["tran_type"].ToString();
                        info.ref_no = drc.Rows[i]["ref_no"].ToString();
                        info.tran_date = cn.ConvertNullDateTime(drc.Rows[i]["tran_date"]) ?? new DateTime((cn.ConvertNullInt(drc.Rows[i]["year_no"]) ?? 0) - 543, cn.ConvertNullInt(drc.Rows[i]["month_no"]) ?? 0, 1).AddMonths(1).AddDays(-1);//cn.ConvertNullDateTime(drc.Rows[i]["tran_date"]);
                        info.tran_name = drc.Rows[i]["tran_name"].ToString();
                        info.work_area_seq = cn.ConvertNullInt(drc.Rows[i]["work_area_seq"]);
                        info.bgm_source_seq = cn.ConvertNullInt(drc.Rows[i]["bgm_source_seq"]);
                        info.ref_seq = cn.ConvertNullInt(drc.Rows[i]["ref_seq"]);
                        info.bgm_year = cn.ConvertNullInt(drc.Rows[i]["bgm_year"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public IList<M_ACM_GENERATE_ACCOUNT_Auto> GetListDetail(Nullable<Int32> ref_seq, string ref_type)
        {
            DataTable dtb = null;
            string strSQL = "";
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@ref_seq", ref_seq);
            IList<M_ACM_GENERATE_ACCOUNT_Auto> infoList = new List<M_ACM_GENERATE_ACCOUNT_Auto>();
            try
            {
                dbManager.Open();

                string strEventSeq = "";

                switch (ref_type)
                {
                    #region fim_petitions
                    case "fim_petitions":
                        strSQL = "SELECT ba.wait_income_bb_account_seq as account_seq"
                            + ", acc.account_code"
                            + ", acc.account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", ptd.amount as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_petitions pt"
                            + " LEFT JOIN (select petition_seq"
                                + " , sum(coalesce(amount_real, 0)) as amount"
                                + " from fim_petition_dtls"
                                + " where record_status not like 'D'"
                                + " group by petition_seq"
                                + " ) ptd ON pt.petition_seq = ptd.petition_seq"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc ON ba.wait_income_bb_account_seq = acc.account_seq"
                            + " WHERE pt.petition_seq = @ref_seq"

                            + " union all"

                            + " SELECT ba.income_bb_account_seq as account_seq"
                            + ", acc.account_code"
                            + ", acc.account_name"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", ptd.amount as amount_cr"
                            + " FROM fim_petitions pt"
                            + " LEFT JOIN (select petition_seq"
                                + " , sum(coalesce(amount_real, 0)) as amount"
                                + " from fim_petition_dtls"
                                + " where record_status not like 'D'"
                                + " group by petition_seq"
                                + " ) ptd ON pt.petition_seq = ptd.petition_seq"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc ON ba.income_bb_account_seq = acc.account_seq"
                            + " WHERE pt.petition_seq = @ref_seq";
                        break;
                    #endregion
                    #region fim_money_requests
                    case "fim_money_requests":
                        strSQL = "SELECT event_seq"
                            + " FROM fim_money_requests"
                            + " WHERE money_req_seq = @ref_seq";
                        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                        if (dtb.Rows.Count > 0)
                        {
                            strEventSeq = dtb.Rows[0]["event_seq"].ToString();
                        }

                        switch (strEventSeq)
                        {
                            case "11":
                                strSQL = "SELECT ba.direct_pay_account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(mrd.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + ", sum(coalesce(mrd.amount_vat, 0)) as amount_vat"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.direct_pay_account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq"

                                    + " UNION ALL"

                                    + " SELECT ba.dn_cr_account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", mrd.amount as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.dn_cr_account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq";
                                break;
                            case "28":
                                strSQL = "SELECT exp.account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(mrd.amount, 0) - coalesce(mrd.amount_vat, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq, mrd.bgm_proj_oper_exp_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + ", sum(coalesce(mrd.amount_vat, 0)) as amount_vat"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq, mrd.bgm_proj_oper_exp_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
                                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                                    + " LEFT JOIN acm_mas_account acc ON exp.account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq"

                                    + " UNION ALL"

                                    + " SELECT ba.payroll_tax_account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", mrd.amount as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.payroll_tax_account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq";
                                break;
                            case "12":
                            case "14":
                            case "15":
                            case "17":
                            case "18":
                            default:
                                strSQL = "SELECT exp.account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(mrd.amount, 0) - coalesce(mrd.amount_vat, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq, mrd.bgm_proj_oper_exp_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + ", sum(coalesce(mrd.amount_vat, 0)) as amount_vat"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq, mrd.bgm_proj_oper_exp_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " LEFT JOIN bgm_project_operation_expense pjexp ON mrd.bgm_proj_oper_exp_seq = pjexp.bgm_proj_oper_exp_seq"
                                    + " LEFT JOIN bgm_project_expense recexp ON pjexp.bgm_proj_exp_seq = recexp.bgm_proj_exp_seq"
                                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                                    + " LEFT JOIN acm_mas_account acc ON exp.account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq"

                                    + " UNION ALL"

                                    + " SELECT ba.suspense_input_tax_account_seq as account_seq"
                                    + ", acc2.account_code as account_code"
                                    + ", acc2.account_name as account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(mrd.amount_vat, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount_vat, 0)) as amount_vat"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.suspense_input_tax_account_seq = acc2.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq"

                                    + " UNION ALL"

                                    + " SELECT ba.remain_account_seq as account_seq"
                                    + ", acc.account_code, acc.account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", mrd.amount as amount_cr"
                                    + " FROM fim_money_requests mr"
                                    + " LEFT JOIN (select mrd.money_req_seq"
                                        + ", sum(coalesce(mrd.amount, 0)) as amount"
                                        + " from fim_money_request_dtls mrd"
                                        + " where mrd.record_status not like 'D'"
                                        + " group by mrd.money_req_seq"
                                        + ") mrd ON mr.money_req_seq = mrd.money_req_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.remain_account_seq = acc.account_seq"
                                    + " WHERE mr.money_req_seq = @ref_seq";
                                break;
                        }
                        break;
                    #endregion
                    #region fim_payments
                    case "fim_payments":
                        bool chkTaxInvoice = true;
                        strSQL = "SELECT p.event_seq, mr.chk_tax_invoice, dn.ChkTaxInvoice"
                            + " from fim_payments p"
                            + " left join fim_money_requests mr on p.money_req_seq = mr.money_req_seq"
                            + " left join IcmDeliveryNote dn on mr.icm_dn_seq = dn.DnId"
                            + " WHERE pay_seq = @ref_seq";
                        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                        if (dtb.Rows.Count > 0)
                        {
                            strEventSeq = dtb.Rows[0]["event_seq"].ToString();
                            if (strEventSeq == "11")
                            {
                                chkTaxInvoice = Convert.ToBoolean(cn.ConvertNullInt(dtb.Rows[0]["ChkTaxInvoice"]) ?? 1);
                            }
                            else
                            {
                                chkTaxInvoice = Convert.ToBoolean(cn.ConvertNullInt(dtb.Rows[0]["chk_tax_invoice"]) ?? 1);
                            }
                        }

                        strSQL = "";

                        switch (strEventSeq)
                        {
                            case "11":
                                // ค่าใช้จ่าย
                                strSQL = "SELECT ba.dn_cr_account_seq as account_seq"
                                    + ", acc1.account_code as account_code"
                                    + ", acc1.account_name account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.amount, 0) + coalesce(pay.tax, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON ba.dn_cr_account_seq = acc1.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"

                                    + " SELECT case when c.ConGrpId is null or c.ConGrpId = 47 then ba.personal_tax_account_seq"
                                        + " else ba.company_tax_account_seq"
                                        + " end as account_seq"
                                    + ", case when c.ConGrpId is null or c.ConGrpId = 47 then acc1.account_code"
                                        + " else acc2.account_code"
                                        + " end as account_code"
                                    + ", case when c.ConGrpId is null or c.ConGrpId = 47 then acc1.account_name"
                                        + " else acc2.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.tax as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN IcmMasContact c ON pay.contact_seq = c.ConId"
                                    + " LEFT JOIN acm_mas_account acc1 ON ba.personal_tax_account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.company_tax_account_seq = acc2.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                            case "21":
                                strSQL = "SELECT acc.account_seq"
                                    + ", acc.account_code"
                                    + ", acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(tax.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " left join (select pay.tax_pay_seq"
                                        + ", sum(pay.tax) as amount"
                                        + " from fim_payments pay"
                                        + " left join IcmMasContact c on pay.contact_seq = c.ConId"
                                        + " where c.ConGrpId = 47"
                                        + " group by pay.tax_pay_seq"
                                        + ") tax on pay.pay_seq = tax.tax_pay_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.personal_tax_account_seq = acc.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"

                                    + " SELECT acc.account_seq"
                                    + ", acc.account_code"
                                    + ", acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(tax.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " left join (select pay.tax_pay_seq"
                                        + ", sum(pay.tax) as amount"
                                        + " from fim_payments pay"
                                        + " left join IcmMasContact c on pay.contact_seq = c.ConId"
                                        + " where c.ConGrpId <> 47"
                                        + " group by pay.tax_pay_seq"
                                        + ") tax on pay.pay_seq = tax.tax_pay_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.company_tax_account_seq = acc.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                            case "26":
                                strSQL = "select ba.revenue_vat_creditor_account_seq as account_seq"
                                    + ", acc.account_code as account_code"
                                    + ", acc.account_name as account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " from fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " left join acm_mas_account acc on ba.revenue_vat_creditor_account_seq = acc.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                            case "27":
                                strSQL = "SELECT distinct rt.account_seq"
                                    + ", acc1.account_code"
                                    + ", acc1.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.amount, 0) - coalesce(pay.vat, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " inner join fim_receives r on pay.pay_seq = r.pay_seq"
                                    + " inner join fim_mas_receive_types rt on r.receive_type_seq = rt.receive_type_seq"
                                    + " LEFT JOIN acm_mas_account acc1 ON rt.account_seq = acc1.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"

                                    + " SELECT ba.sale_vat_account_seq as account_seq"
                                    + ", acc1.account_code as account_code"
                                    + ", acc1.account_name as account_code"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.vat, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON ba.sale_vat_account_seq = acc1.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    + " and coalesce(pay.vat, 0) > 0"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                            case "28":
                                strSQL = "select ba.payroll_tax_account_seq as account_seq"
                                    + ", acc.account_code as account_code"
                                    + ", acc.account_name as account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " from fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " left join acm_mas_account acc on ba.payroll_tax_account_seq = acc.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                            default:
                                // ค่าใช้จ่าย
                                strSQL = "SELECT case pay.event_seq when 16 then ba.loan_account_seq"
                                        + " else ba.remain_account_seq"
                                        + " end as account_seq"

                                    + ", case pay.event_seq when 16 then acc1.account_code"
                                        + " else acc2.account_code"
                                        + " end as account_code"

                                    + ", case pay.event_seq when 16 then acc1.account_name"
                                        + " else acc2.account_name"
                                        + " end as account_name"

                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(pay.amount, 0) + coalesce(pay.tax, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON ba.loan_account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.remain_account_seq = acc2.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // เครดิต เงินสด/เงินฝาก
                                    + " SELECT case pay.method_seq"
                                        + " when 1 then ba.cash_account_seq"
                                        + " when 2 then bacc.account_seq"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then bacc3.account_seq"
                                                        + " when '1' then ba.income_bb_account_seq"
                                                        + " end"
                                        + " when 4 then bacc2.account_seq"
                                        + " end as account_seq"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_code"
                                        + " when 2 then acc1.account_code"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_code"
                                                        + " when '1' then acc3.account_code"
                                                        + " end"
                                        + " when 4 then acc4.account_code"
                                        + " end as account_code"
                                    + ", case pay.method_seq"
                                        + " when 1 then acc2.account_name"
                                        + " when 2 then acc1.account_name"
                                        + " when 3 then case coalesce(pet.direct_pay, '0')"
                                                        + " when '0' then acc5.account_name"
                                                        + " when '1' then acc3.account_name"
                                                        + " end"
                                        + " when 4 then acc4.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.amount as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " LEFT JOIN fim_payment_cheques pc on pay.pay_seq = pc.pay_seq"
                                        + " and pc.record_status not like 'D'"
                                    + " LEFT JOIN fim_payments_payinpack pip on pay.pay_seq = pip.pay_seq"
                                        + " and pip.record_status not like 'D'"
                                    + " LEFT JOIN fim_cheques che on pc.cheque_seq = che.cheque_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc on che.bank_acc_seq = bacc.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on pip.bank_acc_seq = bacc2.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc3 on pay.from_bank_acc_id = bacc3.bank_acc_seq"
                                    + " LEFT JOIN fim_petitions pet on pay.petition_seq = pet.petition_seq"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.cash_account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.income_bb_account_seq = acc3.account_seq"
                                    + " LEFT JOIN acm_mas_account acc4 ON bacc2.account_seq = acc4.account_seq"
                                    + " LEFT JOIN acm_mas_account acc5 ON bacc3.account_seq = acc5.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"

                                    + " union all"
                                    // Tax
                                    + " SELECT case when c.ConGrpId is null or c.ConGrpId = 47 then ba.personal_tax_account_seq"
                                        + " else ba.company_tax_account_seq"
                                        + " end as account_seq"
                                    + ", case when c.ConGrpId is null or c.ConGrpId = 47 then acc1.account_code"
                                        + " else acc2.account_code"
                                        + " end as account_code"
                                    + ", case when c.ConGrpId is null or c.ConGrpId = 47 then acc1.account_name"
                                        + " else acc2.account_name"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", pay.tax as amount_cr"
                                    + " FROM fim_payments pay"
                                    + " cross join acm_mas_base_account ba"
                                    + " LEFT JOIN IcmMasContact c ON pay.contact_seq = c.ConId"
                                    + " LEFT JOIN acm_mas_account acc1 ON ba.personal_tax_account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON ba.company_tax_account_seq = acc2.account_seq"
                                    + " WHERE pay.pay_seq = @ref_seq"
                                    ;
                                break;
                        }

                        //if (!chkTaxInvoice)
                        //{
                        strSQL += (strEventSeq != "27" ? " union all"
                            + " SELECT ba.buy_vat_account_seq as account_seq"
                            + ", acc1.account_code as account_code"
                            + ", acc1.account_name as account_code"
                            + ", 'Dr' as dr_or_cr"
                            + ", coalesce(pay.vat, 0) as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_payments pay"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON ba.buy_vat_account_seq = acc1.account_seq"
                            + " WHERE pay.pay_seq = @ref_seq"
                            + " and coalesce(pay.vat, 0) > 0"

                            + " union all"

                            + " SELECT ba.suspense_input_tax_account_seq as account_seq"
                            + ", acc1.account_code as account_code"
                            + ", acc1.account_name as account_code"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", coalesce(pay.vat, 0) as amount_cr"
                            + " FROM fim_payments pay"
                            + " cross join acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON ba.suspense_input_tax_account_seq = acc1.account_seq"
                            + " WHERE pay.pay_seq = @ref_seq"
                            + " and coalesce(pay.vat, 0) > 0" : "");
                        //}

                        break;
                    #endregion
                    #region fim_receives
                    case "fim_receives":
                        // bank or cash
                        strSQL = "SELECT case when r.method_seq = 1 or r.method_seq = 2"
                                + " then cv.cash_account_seq"
                                + " else ba.account_seq"
                                + " end as account_seq"
                            + ", case when r.method_seq = 1 or r.method_seq = 2"
                                + " then acc1.account_code"
                                + " else acc2.account_code"
                                + " end as account_code"
                            + ", case when r.method_seq = 1 or r.method_seq = 2"
                                + " then acc1.account_name"
                                + " else acc2.account_name"
                                + " end as account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", r.amount as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_receives r"
                            + " LEFT JOIN fim_mas_bank_accounts ba ON r.bank_acc_seq = ba.bank_acc_seq"
                            + " cross join acm_mas_base_account cv"
                            + " LEFT JOIN acm_mas_account acc1 ON cv.cash_account_seq = acc1.account_seq"
                            + " LEFT JOIN acm_mas_account acc2 ON ba.account_seq = acc2.account_seq"
                            + " WHERE r.receive_seq = @ref_seq"
                            // tax
                            + " union all"

                            + " SELECT case c.ConGrpId"
                                + " when 47 then cv.personal_tax_account_seq"
                                + " else cv.company_tax_account_seq"
                                + " end as account_seq"
                            + ", case c.ConGrpId"
                                + " when 47 then acc1.account_code"
                                + " else acc2.account_code"
                                + " end as account_code"
                            + ", case c.ConGrpId"
                                + " when 47 then acc1.account_name"
                                + " else acc2.account_name"
                                + " end as account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", r.tax as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_receives r"
                            + " cross join acm_mas_base_account cv"
                            + " LEFT JOIN IcmMasContact c ON r.contact_seq = c.ConId"
                            + " LEFT JOIN acm_mas_account acc1 ON cv.personal_tax_account_seq = acc1.account_seq"
                            + " LEFT JOIN acm_mas_account acc2 ON cv.company_tax_account_seq = acc2.account_seq"
                            + " WHERE r.receive_seq = @ref_seq"
                            // income
                            + " union all"

                            + " SELECT case r.receive_mode"
                                + " when 1 then case when mr.event_seq = 16 then cv.loan_account_seq"
                                    + " else cv.over_account_seq"
                                    + " end"
                                + " when 2 then rt.account_seq"
                                + " when 3 then cv.wait_income_bb_account_seq"
                                + " end as account_seq"
                            + ", case r.receive_mode"
                                + " when 1 then case when mr.event_seq = 16 then acc4.account_code"
                                    + " else acc1.account_code"
                                    + " end"
                                + " when 2 then acc2.account_code"
                                + " when 3 then acc3.account_code"
                                + " end as account_code"
                            + ", case r.receive_mode"
                                + " when 1 then case when mr.event_seq = 16 then acc4.account_name"
                                    + " else acc1.account_name"
                                    + " end"
                                + " when 2 then acc2.account_name"
                                + " when 3 then acc3.account_name"
                                + " end as account_name"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", r.amount - coalesce(r.vat_amount, 0) as amount_cr"
                            + " FROM fim_receives r"
                            + " LEFT JOIN fim_money_requests mr on r.money_req_seq = mr.money_req_seq"
                            + " cross join acm_mas_base_account cv"
                            + " LEFT JOIN fim_mas_receive_types rt on r.receive_type_seq = rt.receive_type_seq"
                            + " LEFT JOIN acm_mas_account acc1 ON cv.over_account_seq = acc1.account_seq"
                            + " LEFT JOIN acm_mas_account acc2 ON rt.account_seq = acc2.account_seq"
                            + " LEFT JOIN acm_mas_account acc3 ON cv.wait_income_bb_account_seq = acc3.account_seq"
                            + " LEFT JOIN acm_mas_account acc4 ON cv.loan_account_seq = acc4.account_seq"
                            + " WHERE r.receive_seq = @ref_seq"
                            // tax
                            + " union all"

                            + " SELECT acc1.account_seq as account_seq"
                            + ", acc1.account_code as account_code"
                            + ", acc1.account_name as account_name"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", r.vat_amount as amount_cr"
                            + " FROM fim_receives r"
                            + " cross join acm_mas_base_account cv"
                            + " LEFT JOIN acm_mas_account acc1 ON cv.sale_vat_account_seq = acc1.account_seq"
                            + " WHERE r.receive_seq = @ref_seq";
                        break;
                    #endregion
                    #region fim_money_returns
                    case "fim_money_returns":
                        strSQL = "SELECT exp.account_seq"
                            + ", acc1.account_code"
                            + ", acc1.account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", sum(coalesce(retd.amount, 0) - coalesce(retd.vat_amount, 0)) as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_money_returns ret"
                            + " LEFT JOIN fim_money_return_dtls retd on ret.return_seq = retd.return_seq"
                                + " and retd.record_status not like 'D'"
                            + " LEFT JOIN fim_money_request_dtls mrd on retd.money_req_dtls_seq = mrd.money_req_dtls_seq"
                            + " LEFT JOIN bgm_project_operation_expense opexp on mrd.bgm_proj_oper_exp_seq = opexp.bgm_proj_oper_exp_seq"
                            + " LEFT JOIN bgm_project_expense pjexp on opexp.bgm_proj_exp_seq = pjexp.bgm_proj_exp_seq"
                            + " LEFT JOIN bgm_mas_expense exp on pjexp.bgm_exp_seq = exp.bgm_exp_seq"
                            + " LEFT JOIN acm_mas_account acc1 ON exp.account_seq = acc1.account_seq"
                            + " WHERE ret.return_seq = @ref_seq"
                            + " group by exp.account_seq, acc1.account_code, acc1.account_name"

                            + " union all"

                            + " SELECT acc1.account_seq"
                            + ", acc1.account_code"
                            + ", acc1.account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", sum(coalesce(retd.vat_amount, 0)) as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_money_returns ret"
                            + " LEFT JOIN fim_money_return_dtls retd on ret.return_seq = retd.return_seq"
                                + " and retd.record_status not like 'D'"
                            + " cross JOIN acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON ba.buy_vat_account_seq = acc1.account_seq"
                            + " WHERE ret.return_seq = @ref_seq"
                            + " group by acc1.account_seq, acc1.account_code, acc1.account_name"

                            + " union all"

                            + " SELECT ba.loan_account_seq as account_seq"
                            + ", acc1.account_code"
                            + ", acc1.account_name"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", retd.amount as amount_cr"
                            + " FROM fim_money_returns ret"
                            + " LEFT JOIN (select retd.return_seq"
                                + ", sum(coalesce(retd.amount, 0)) as amount"
                                + " from fim_money_return_dtls retd"
                                + " where retd.record_status not like 'D'"
                                + " group by retd.return_seq"
                                + ") retd on ret.return_seq = retd.return_seq"
                            + " CROSS JOIN acm_mas_base_account ba"
                            + " LEFT JOIN acm_mas_account acc1 ON ba.loan_account_seq = acc1.account_seq"
                            + " WHERE ret.return_seq = @ref_seq";
                        break;
                    #endregion
                    #region icm_delivery_note
                    case "icm_delivery_note":
                        dbManager.CreateParameters(1);
                        dbManager.AddParameters(0, "@icm_dn_seq", ref_seq);
                        strSQL = @"
select poii.BidderQuoId, coalesce(dni.HaveItem, 1) as HaveItem
from IcmDeliveryNoteItem dni
left join IcmPurchaseOrderInstallmentItem poii on dni.PoInstallItemId = poii.PoInstallItemId
where dni.DnId = @icm_dn_seq
";
                        DataTable dtbQuotation = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                        for (int i = 0; i < dtbQuotation.Rows.Count; i++)
                        {
                            // มีงานระหว่างทำหรือไม่
                            dbManager.CreateParameters(2);
                            dbManager.AddParameters(0, "@BidderQuoId", dtbQuotation.Rows[i]["BidderQuoId"].ToString());
                            dbManager.AddParameters(1, "@DnId", ref_seq);
                            strSQL = @"
select count(*) as row_count
from IcmPurchaseOrderInstallmentItem poii
left join IcmDeliveryNoteItem dni on poii.PoInstallItemId = dni.PoInstallItemId
where poii.BidderQuoId = @BidderQuoId
and HaveItem = 0
";
                            int nRowCount = Convert.ToInt32(dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0]);

                            // ถ้าไม่เป็นงานระหว่างทำ จะต้องเช็คว่ามีงานระหว่างทำรึเปล่า
                            if (dtbQuotation.Rows[i]["HaveItem"].ToString() == "1" && nRowCount > 0)
                            {
                                // ถ้ามีงานระหว่างทำ ให้ลงงานระหว่างทำก่อน
                                strSQL = @"
select ba.wait_account_seq as account1, ba.suspense_input_tax_account_seq as account3, ba.direct_pay_account_seq as account4
, acc1.account_code as code1, acc3.account_code as code3, acc4.account_code as code4
, acc1.account_name as name1, acc3.account_name as name3, acc4.account_name as name4
, coalesce(dni.NetAmount, 0) as NetAmount, coalesce(dni.VatAmount, 0) as VatAmount
from IcmDeliveryNoteItem dni
left join IcmPurchaseOrderInstallmentItem poii on dni.PoInstallItemId = poii.PoInstallItemId
cross join acm_mas_base_account ba
left join acm_mas_account acc1 on ba.wait_account_seq = acc1.account_seq
left join acm_mas_account acc3 on ba.suspense_input_tax_account_seq = acc3.account_seq
left join acm_mas_account acc4 on ba.direct_pay_account_seq = acc4.account_seq
where poii.BidderQuoId = @BidderQuoId
and dni.DnId = @DnId
";
                                DataTable dtbDni = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                                if (dtbDni.Rows.Count > 0)
                                {
                                    M_ACM_GENERATE_ACCOUNT_Auto info1 = new M_ACM_GENERATE_ACCOUNT_Auto()
                                    {
                                        account_seq = cn.ConvertNullInt(dtbDni.Rows[0]["account1"]),
                                        dr_or_cr = "Dr",
                                        account_code = dtbDni.Rows[0]["code1"].ToString(),
                                        account_name = dtbDni.Rows[0]["name1"].ToString(),
                                        amount_dr = cn.ConvertNullDouble(dtbDni.Rows[0]["NetAmount"]) - cn.ConvertNullDouble(dtbDni.Rows[0]["VatAmount"]),
                                        amount_cr = 0,
                                    };

                                    infoList.Add(info1);

                                    if (cn.ConvertNullDouble(dtbDni.Rows[0]["VatAmount"]) != 0)
                                    {
                                        M_ACM_GENERATE_ACCOUNT_Auto info2 = new M_ACM_GENERATE_ACCOUNT_Auto()
                                        {
                                            account_seq = cn.ConvertNullInt(dtbDni.Rows[0]["account3"]),
                                            dr_or_cr = "Dr",
                                            account_code = dtbDni.Rows[0]["code3"].ToString(),
                                            account_name = dtbDni.Rows[0]["name3"].ToString(),
                                            amount_dr = cn.ConvertNullDouble(dtbDni.Rows[0]["VatAmount"]),
                                            amount_cr = 0,
                                        };

                                        infoList.Add(info2);
                                    }

                                    M_ACM_GENERATE_ACCOUNT_Auto info3 = new M_ACM_GENERATE_ACCOUNT_Auto()
                                    {
                                        account_seq = cn.ConvertNullInt(dtbDni.Rows[0]["account4"]),
                                        dr_or_cr = "Cr",
                                        account_code = dtbDni.Rows[0]["code4"].ToString(),
                                        account_name = dtbDni.Rows[0]["name4"].ToString(),
                                        amount_dr = 0,
                                        amount_cr = cn.ConvertNullDouble(dtbDni.Rows[0]["NetAmount"]),
                                    };

                                    infoList.Add(info3);
                                }

                                dbManager.CreateParameters(1);
                                dbManager.AddParameters(0, "@BidderQuoId", dtbQuotation.Rows[i]["BidderQuoId"].ToString());

                                strSQL = @"
select sum(coalesce(dni.NetAmount, 0)) as Amount
, sum(coalesce(dni.VatAmount, 0)) as VatAmount
, ba.wait_account_seq as account_seq1
, case when ri.ResItemId is not null and rg.ResTypeId <> 1
	then rg.AssetAccountId
	else ex.account_seq
	end as account_seq2
, acc1.account_code as account_code1
, case when ri.ResItemId is not null and rg.ResTypeId <> 1
	then acc1.account_code
	else acc2.account_code
	end as account_code2
, acc1.account_name as account_name1
, case when ri.ResItemId is not null and rg.ResTypeId <> 1
	then acc1.account_name
	else acc2.account_name
	end as account_name2
from IcmPurchaseOrderInstallmentItem poii
left join IcmDeliveryNoteItem dni on poii.PoInstallItemId = dni.PoInstallItemId
left join IcmBidderQuotation q on poii.BidderQuoId = q.BidderQuoId
left join IcmPurchaseRequestItem ri on q.PrItemId = ri.PrItemId
left join bgm_mas_expense ex on ri.BgExpId = ex.bgm_exp_seq
left join IcmMasResourceItem mi on ri.ResItemId = mi.ResItemId
left join IcmMasResourceSubGroup sg on mi.ResSubGrpId = sg.ResSubGrpId
left join IcmMasResourceGroup rg on sg.ResGrpId = rg.ResGrpId
LEFT JOIN acm_mas_account acc1 ON rg.AssetAccountId = acc1.account_seq
LEFT JOIN acm_mas_account acc2 ON ex.account_seq = acc2.account_seq
cross join acm_mas_base_account ba
where poii.BidderQuoId = @BidderQuoId
group by ba.wait_account_seq, ri.ResItemId, rg.ResTypeId, rg.AssetAccountId, ex.account_seq, acc1.account_code, acc2.account_code, acc1.account_name, acc2.account_name
";
                                DataTable dtbDni2 = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                                double dAmount = cn.ConvertNullDouble(dtbDni2.Rows[0][0]) ?? 0;
                                double dVatAmount = cn.ConvertNullDouble(dtbDni2.Rows[0][1]) ?? 0;
                                string strSeq1 = dtbDni2.Rows[0][2].ToString();
                                string strSeq2 = dtbDni2.Rows[0][3].ToString();
                                string strCode1 = dtbDni2.Rows[0][4].ToString();
                                string strCode2 = dtbDni2.Rows[0][5].ToString();
                                string strName1 = dtbDni2.Rows[0][6].ToString();
                                string strName2 = dtbDni2.Rows[0][7].ToString();

                                if (dtbDni2.Rows.Count > 0)
                                {
                                    M_ACM_GENERATE_ACCOUNT_Auto info1 = new M_ACM_GENERATE_ACCOUNT_Auto()
                                    {
                                        account_seq = Convert.ToInt32(strSeq2),
                                        dr_or_cr = "Dr",
                                        account_code = strCode2,
                                        account_name = strName2,
                                        amount_dr = dAmount - dVatAmount,
                                        amount_cr = 0,
                                    };

                                    infoList.Add(info1);

                                    M_ACM_GENERATE_ACCOUNT_Auto info2 = new M_ACM_GENERATE_ACCOUNT_Auto()
                                    {
                                        account_seq = Convert.ToInt32(strSeq1),
                                        dr_or_cr = "Cr",
                                        account_code = strCode1,
                                        account_name = strName1,
                                        amount_dr = 0,
                                        amount_cr = dAmount - dVatAmount,
                                    };

                                    infoList.Add(info2);
                                }
                            }
                            else
                            {
                                dbManager.CreateParameters(2);
                                dbManager.AddParameters(0, "@BidderQuoId", dtbQuotation.Rows[i]["BidderQuoId"].ToString());
                                dbManager.AddParameters(1, "@DnId", ref_seq);

                                strSQL = @"
select account_seq1, account_code1, account_name1
, account_seq3
, account_code3
, account_name3
, account_seq4
, account_code4
, account_name4
, account_seq5
, account_code5
, account_name5
, account_seq6
, account_code6
, account_name6
, HaveItem
, sum(coalesce(amount, 0)) as amount
, sum(coalesce(amount_vat, 0)) as amount_vat
, coalesce(amount_penalty, 0) as amount_penalty
from (SELECT case when ri.ResItemId is not null and rg.ResTypeId <> 1
	then rg.AssetAccountId
	else ex.account_seq
	end as account_seq1
, case when ri.ResItemId is not null and rg.ResTypeId <> 1
	then acc1.account_code
	else acc2.account_code
	end as account_code1
, case when ri.ResItemId is not null and rg.ResTypeId <> 1
	then acc1.account_name
	else acc2.account_name
	end as account_name1
, acc3.account_seq as account_seq3
, acc3.account_code as account_code3
, acc3.account_name as account_name3
, acc4.account_seq as account_seq4
, acc4.account_code as account_code4
, acc4.account_name as account_name4
, acc5.account_seq as account_seq5
, acc5.account_code as account_code5
, acc5.account_name as account_name5
, acc6.account_seq as account_seq6
, acc6.account_code as account_code6
, acc6.account_name as account_name6
, coalesce(ni.NetAmount, 0) as amount
, COALESCE(ni.VatAmount, 0) as amount_vat
, coalesce(pen.amount, 0) as amount_penalty
, ni.HaveItem

FROM IcmDeliveryNoteItem ni
left join IcmPurchaseOrderInstallmentItem ii on ni.PoInstallItemId = ii.PoInstallItemId
left join IcmBidderQuotation q on ii.BidderQuoId = q.BidderQuoId
left join IcmPurchaseRequestItem ri on q.PrItemId = ri.PrItemId
left join bgm_mas_expense ex on ri.BgExpId = ex.bgm_exp_seq
left join IcmMasResourceItem mi on ri.ResItemId = mi.ResItemId
left join IcmMasResourceSubGroup sg on mi.ResSubGrpId = sg.ResSubGrpId
left join IcmMasResourceGroup rg on sg.ResGrpId = rg.ResGrpId
LEFT JOIN acm_mas_account acc1 ON rg.AssetAccountId = acc1.account_seq
LEFT JOIN acm_mas_account acc2 ON ex.account_seq = acc2.account_seq
cross join acm_mas_base_account ba
LEFT JOIN acm_mas_account acc3 ON ba.suspense_input_tax_account_seq = acc3.account_seq
LEFT JOIN acm_mas_account acc4 ON ba.direct_pay_account_seq = acc4.account_seq
LEFT JOIN acm_mas_account acc5 ON ba.wait_account_seq = acc5.account_seq
LEFT JOIN acm_mas_account acc6 ON ba.income_from_penalty_account_seq = acc6.account_seq
left join (select DnId, sum(coalesce(PenaltyFinalAmount, 0)) as amount
    from IcmDeliveryNotePenalty
    group by DnId
    ) pen on ni.DnId = pen.DnId
WHERE ni.DnId = @DnId
and ii.BidderQuoId = @BidderQuoId
) t
group by account_seq1, account_code1, account_name1
, account_seq3
, account_code3
, account_name3
, account_seq4
, account_code4
, account_name4
, account_seq5
, account_code5
, account_name5
, account_seq6
, account_code6
, account_name6
, HaveItem, amount_penalty";
                                DataTable dtbDNDtls = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                                foreach (DataRow dr in dtbDNDtls.Rows)
                                {
                                    if (dr["HaveItem"].ToString() == "False")
                                    {
                                        M_ACM_GENERATE_ACCOUNT_Auto info1 = new M_ACM_GENERATE_ACCOUNT_Auto()
                                        {
                                            account_seq = cn.ConvertNullInt(dr["account_seq5"]),
                                            dr_or_cr = "Dr",
                                            account_code = dr["account_code5"].ToString(),
                                            account_name = dr["account_name5"].ToString(),
                                            amount_dr = cn.ConvertNullDouble(dr["amount"]) - cn.ConvertNullDouble(dr["amount_vat"]) ?? 0,
                                            amount_cr = 0,
                                        };
                                        infoList.Add(info1);
                                    }
                                    else
                                    {
                                        M_ACM_GENERATE_ACCOUNT_Auto info1 = new M_ACM_GENERATE_ACCOUNT_Auto()
                                        {
                                            account_seq = cn.ConvertNullInt(dr["account_seq1"]),
                                            dr_or_cr = "Dr",
                                            account_code = dr["account_code1"].ToString(),
                                            account_name = dr["account_name1"].ToString(),
                                            amount_dr = cn.ConvertNullDouble(dr["amount"]) - cn.ConvertNullDouble(dr["amount_vat"]) ?? 0,
                                            amount_cr = 0,
                                        };
                                        infoList.Add(info1);
                                    }

                                    if (cn.ConvertNullDouble(dr["amount_vat"]) != 0)
                                    {
                                        M_ACM_GENERATE_ACCOUNT_Auto info2 = new M_ACM_GENERATE_ACCOUNT_Auto()
                                        {
                                            account_seq = cn.ConvertNullInt(dr["account_seq3"]),
                                            dr_or_cr = "Dr",
                                            account_code = dr["account_code3"].ToString(),
                                            account_name = dr["account_name3"].ToString(),
                                            amount_dr = cn.ConvertNullDouble(dr["amount_vat"]),
                                            amount_cr = 0,
                                        };

                                        infoList.Add(info2);
                                    }

                                    M_ACM_GENERATE_ACCOUNT_Auto info3 = new M_ACM_GENERATE_ACCOUNT_Auto()
                                    {
                                        account_seq = cn.ConvertNullInt(dr["account_seq4"]),
                                        dr_or_cr = "Cr",
                                        account_code = dr["account_code4"].ToString(),
                                        account_name = dr["account_name4"].ToString(),
                                        amount_dr = 0,
                                        amount_cr = cn.ConvertNullDouble(dr["amount"]) - cn.ConvertNullDouble(dr["amount_penalty"]) ?? 0,
                                    };

                                    infoList.Add(info3);

                                    if ((cn.ConvertNullDouble(dr["amount_penalty"]) ?? 0) > 0)
                                    {
                                        M_ACM_GENERATE_ACCOUNT_Auto info4 = new M_ACM_GENERATE_ACCOUNT_Auto()
                                        {
                                            account_seq = cn.ConvertNullInt(dr["account_seq6"]),
                                            dr_or_cr = "Cr",
                                            account_code = dr["account_code6"].ToString(),
                                            account_name = dr["account_name6"].ToString(),
                                            amount_dr = 0,
                                            amount_cr = cn.ConvertNullDouble(dr["amount_penalty"]),
                                        };

                                        infoList.Add(info4);
                                    }
                                }
                            }
                        }

                        #region old
                        //                        strSQL = "select account_seq, account_code, account_name, dr_or_cr"
                        //                            + ", sum(coalesce(amount_dr, 0)) as amount_dr"
                        //                            + ", null as amount_cr"
                        //                            + @" from (SELECT case when coalesce(ni.HaveItem, 1) = 1 
                        //	then case when ri.ResItemId is not null and rg.ResTypeId <> 1
                        //		then rg.AssetAccountId
                        //		else ex.account_seq
                        //		end
                        //	else acc3.account_seq end as account_seq
                        //, case when coalesce(ni.HaveItem, 1) = 1 
                        //	then case when ri.ResItemId is not null and rg.ResTypeId <> 1
                        //		then acc1.account_code
                        //		else acc2.account_code
                        //		end
                        //	else acc3.account_code end as account_code
                        //, case when coalesce(ni.HaveItem, 1) = 1 
                        //	then case when ri.ResItemId is not null and rg.ResTypeId <> 1
                        //		then acc1.account_name
                        //		else acc2.account_name
                        //		end
                        //	else acc3.account_name end as account_name"
                        //                            + ", 'Dr' as dr_or_cr"
                        //                            + ", coalesce(ni.NetAmount, 0) - COALESCE (ni.VatAmount, 0) as amount_dr"
                        //                            + ", null as amount_cr"

                        //                            + " FROM IcmDeliveryNoteItem ni"
                        //                            + " left join IcmPurchaseOrderInstallmentItem ii on ni.PoInstallItemId = ii.PoInstallItemId"
                        //                            + " left join IcmBidderQuotation q on ii.BidderQuoId = q.BidderQuoId"
                        //                            + " left join IcmPurchaseRequestItem ri on q.PrItemId = ri.PrItemId"
                        //                            + " left join bgm_mas_expense ex on ri.BgExpId = ex.bgm_exp_seq"
                        //                            + " left join IcmMasResourceItem mi on ri.ResItemId = mi.ResItemId"
                        //                            + " left join IcmMasResourceSubGroup sg on mi.ResSubGrpId = sg.ResSubGrpId"
                        //                            + " left join IcmMasResourceGroup rg on sg.ResGrpId = rg.ResGrpId"
                        //                            + " CROSS JOIN acm_mas_base_account ba"
                        //                            + " LEFT JOIN acm_mas_account acc1 ON rg.AssetAccountId = acc1.account_seq"
                        //                            + " LEFT JOIN acm_mas_account acc2 ON ex.account_seq = acc2.account_seq"
                        //                            + " LEFT JOIN acm_mas_account acc3 ON ba.wait_account_seq = acc3.account_seq"
                        //                            + " WHERE ni.DnId = @ref_seq"
                        //                            + ") t"
                        //                            + " group by account_seq, account_code, account_name, dr_or_cr"

                        //                            + " union all"
                        //                            + " select ba.suspense_input_tax_account_seq as account_seq"
                        //                            + ", acc2.account_code as account_code"
                        //                            + ", acc2.account_name as account_name"
                        //                            + ", 'Dr' as dr_or_cr"
                        //                            + ", sum(COALESCE(ni.VatAmount, 0)) as amount_dr"
                        //                            + ", null as amount_cr"
                        //                            + " FROM IcmDeliveryNoteItem ni"
                        //                            + " inner join IcmDeliveryNote dn on ni.DnId = dn.DnId"
                        //                            + " cross join acm_mas_base_account ba"
                        //                            + " LEFT JOIN acm_mas_account acc2 ON ba.suspense_input_tax_account_seq = acc2.account_seq"
                        //                            + " WHERE ni.DnId = @ref_seq"
                        //                            + " group by ba.suspense_input_tax_account_seq, acc2.account_code, acc2.account_name"

                        //                            + " union all"

                        //                            + " SELECT bacc.direct_pay_account_seq as account_seq"
                        //                            + ", acc1.account_code"
                        //                            + ", acc1.account_name"
                        //                            + ", 'Cr' as dr_or_cr"
                        //                            + ", null as amount_dr"
                        //                            + ", sum(coalesce(ni.NetAmount, 0)) as amount_cr"

                        //                            + " FROM IcmDeliveryNoteItem ni"
                        //                            + " cross join acm_mas_base_account bacc"
                        //                            + " LEFT JOIN acm_mas_account acc1 ON bacc.direct_pay_account_seq = acc1.account_seq"
                        //                            + " WHERE ni.DnId = @ref_seq"
                        //                            + " GROUP BY bacc.direct_pay_account_seq, acc1.account_code, acc1.account_name";
                        #endregion
                        return infoList;
                        break;
                    #endregion
                    #region fim_transfers
                    case "fim_transfers":
                        strSQL = "select facc.bgm_source_seq as from_bgm_source_seq"
                            + ", tacc.bgm_source_seq as to_bgm_source_seq"
                            + " from fim_transfers tf"
                            + " left join fim_mas_bank_accounts facc on tf.from_bank_acc_seq = facc.bank_acc_seq"
                            + " left join fim_mas_bank_accounts tacc on tf.to_bank_acc_seq = tacc.bank_acc_seq"
                            + " where tf.transfer_seq = " + ref_seq;
                        dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                        if (dtb.Rows.Count > 0)
                        {
                            if (!string.IsNullOrEmpty(dtb.Rows[0][0].ToString()) && !string.IsNullOrEmpty(dtb.Rows[0][1].ToString()) && dtb.Rows[0][0].ToString() != dtb.Rows[0][1].ToString())
                            {
                                strSQL = "SELECT ba.cross_budget_expense_account_seq as account_seq"
                                    + ", acc.account_code"
                                    + ", acc.account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(t.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_transfers t"
                                    + " CROSS JOIN acm_mas_base_account ba"
                                    + " LEFT JOIN acm_mas_account acc ON ba.cross_budget_expense_account_seq = acc.account_seq"

                                    + " WHERE t.transfer_seq = @ref_seq"

                                    + " union all"

                                    + " SELECT bacc1.account_seq as account_seq"
                                    + ", acc1.account_code"
                                    + ", acc1.account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", coalesce(t.amount, 0) as amount_cr"
                                    + " FROM fim_transfers t"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc1 on t.from_bank_acc_seq = bacc1.bank_acc_seq"
                                    + " LEFT JOIN acm_mas_account acc1 ON bacc1.account_seq = acc1.account_seq"

                                    + " WHERE t.transfer_seq = @ref_seq";
                            }
                            else
                            {
                                strSQL = "SELECT case t.transfer_type"
                                        + " when '1' then acc2.account_seq"
                                        + " when '2' then acc3.account_seq"
                                        + " when '3' then acc2.account_seq"
                                        + " else 0"
                                        + " end as account_seq"
                                    + ", case t.transfer_type"
                                        + " when '1' then acc2.account_code"
                                        + " when '2' then acc3.account_code"
                                        + " when '3' then acc2.account_code"
                                        + " else ''"
                                        + " end as account_code"
                                    + ", case t.transfer_type"
                                        + " when '1' then acc2.account_name"
                                        + " when '2' then acc3.account_name"
                                        + " when '3' then acc2.account_name"
                                        + " else ''"
                                        + " end as account_name"
                                    + ", 'Dr' as dr_or_cr"
                                    + ", coalesce(t.amount, 0) as amount_dr"
                                    + ", null as amount_cr"
                                    + " FROM fim_transfers t"
                                    + " CROSS JOIN acm_mas_base_account ba"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc1 on t.from_bank_acc_seq = bacc1.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on t.to_bank_acc_seq = bacc2.bank_acc_seq"

                                    + " LEFT JOIN acm_mas_account acc1 ON bacc1.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON bacc2.account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.cash_account_seq = acc3.account_seq"

                                    + " WHERE t.transfer_seq = @ref_seq"

                                    + " union all"

                                    + " SELECT case t.transfer_type"
                                        + " when '1' then acc3.account_seq"
                                        + " when '2' then acc1.account_seq"
                                        + " when '3' then acc1.account_seq"
                                        + " else 0"
                                        + " end as account_seq"
                                    + ", case t.transfer_type"
                                        + " when '1' then acc3.account_code"
                                        + " when '2' then acc1.account_code"
                                        + " when '3' then acc1.account_code"
                                        + " else ''"
                                        + " end as account_code"
                                    + ", case t.transfer_type"
                                        + " when '1' then acc3.account_name"
                                        + " when '2' then acc1.account_name"
                                        + " when '3' then acc1.account_name"
                                        + " else ''"
                                        + " end as account_name"
                                    + ", 'Cr' as dr_or_cr"
                                    + ", null as amount_dr"
                                    + ", coalesce(t.amount, 0) as amount_cr"
                                    + " FROM fim_transfers t"
                                    + " CROSS JOIN acm_mas_base_account ba"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc1 on t.from_bank_acc_seq = bacc1.bank_acc_seq"
                                    + " LEFT JOIN fim_mas_bank_accounts bacc2 on t.to_bank_acc_seq = bacc2.bank_acc_seq"

                                    + " LEFT JOIN acm_mas_account acc1 ON bacc1.account_seq = acc1.account_seq"
                                    + " LEFT JOIN acm_mas_account acc2 ON bacc2.account_seq = acc2.account_seq"
                                    + " LEFT JOIN acm_mas_account acc3 ON ba.cash_account_seq = acc3.account_seq"

                                    + " WHERE t.transfer_seq = @ref_seq";
                            }
                        }
                        break;
                    #endregion
                    #region fim_cheques
                    case "fim_cheques":
                        strSQL = "SELECT acc.account_seq as account_seq"
                            + ", acc.account_code as account_code"
                            + ", acc.account_name as account_name"
                            + ", 'Dr' as dr_or_cr"
                            + ", coalesce(pc.amount, 0) as amount_dr"
                            + ", null as amount_cr"
                            + " FROM fim_cheques c"
                            + " LEFT JOIN fim_mas_bank_accounts bacc on c.bank_acc_seq = bacc.bank_acc_seq"
                            + " LEFT JOIN acm_mas_account acc ON bacc.account_seq = acc.account_seq"
                            + " left join (select pc.cheque_seq"
                                + ", sum(coalesce(pc.amount, 0)) as amount"
                                + " from fim_payment_cheques pc"
                                + " inner join fim_payments p on pc.pay_seq = p.pay_seq"
                                    + " and p.record_status = 'A'"
                                + " where pc.record_status not like 'D'"
                                + " group by pc.cheque_seq"
                                + ") pc on c.cheque_seq = pc.cheque_seq"
                            + " WHERE c.cheque_seq = @ref_seq"

                            + " union all"

                            + " SELECT acc.account_seq as account_seq"
                            + ", acc.account_code as account_code"
                            + ", acc.account_name as account_name"
                            + ", 'Cr' as dr_or_cr"
                            + ", null as amount_dr"
                            + ", coalesce(pc.amount, 0) as amount_cr"
                            + " FROM fim_cheques c"
                            + " LEFT JOIN fim_mas_bank_accounts bacc on c.bank_acc_seq = bacc.bank_acc_seq"
                            + " LEFT JOIN fim_mas_bank_accounts bacc1 on bacc.saving_bank_acc_seq = bacc1.bank_acc_seq"
                            + " LEFT JOIN acm_mas_account acc ON bacc1.account_seq = acc.account_seq"
                            + " left join (select pc.cheque_seq"
                                + ", sum(coalesce(pc.amount, 0)) as amount"
                                + " from fim_payment_cheques pc"
                                + " inner join fim_payments p on pc.pay_seq = p.pay_seq"
                                    + " and p.record_status = 'A'"
                                + " where pc.record_status not like 'D'"
                                + " group by pc.cheque_seq"
                                + ") pc on c.cheque_seq = pc.cheque_seq"
                            + " WHERE c.cheque_seq = @ref_seq";
                        break;
                    #endregion
                    #region fim_vat
                    case "fim_vat":
                        strSQL = @"
select ba.sale_vat_account_seq as account_seq
, acc.account_code as account_code
, acc.account_name as account_name
, 'Dr' as dr_or_cr
, coalesce(v.vat_sale_amount, 0) as amount_dr
, null as amount_cr
from fim_vat v
cross join acm_mas_base_account ba
left join acm_mas_account acc on ba.sale_vat_account_seq = acc.account_seq
where v.vat_seq = @ref_seq

union all

select ba.revenue_vat_debtor_account_seq as account_seq
, acc.account_code as account_code
, acc.account_name as account_name
, 'Dr' as dr_or_cr
, coalesce(v.vat_buy_amount, 0) - coalesce(v.vat_sale_amount, 0) as amount_dr
, null as amount_cr
from fim_vat v
cross join acm_mas_base_account ba
left join acm_mas_account acc on ba.revenue_vat_debtor_account_seq = acc.account_seq
where coalesce(v.vat_buy_amount, 0) - coalesce(v.vat_sale_amount, 0) > 0
and v.vat_seq = @ref_seq

union all

select ba.buy_vat_account_seq as account_seq
, acc.account_code as account_code
, acc.account_name as account_name
, 'Cr' as dr_or_cr
, null as amount_dr
, coalesce(v.vat_buy_amount, 0) as amount_cr
from fim_vat v
cross join acm_mas_base_account ba
left join acm_mas_account acc on ba.buy_vat_account_seq = acc.account_seq
where v.vat_seq = @ref_seq

union all

select ba.revenue_vat_creditor_account_seq as account_seq
, acc.account_code as account_code
, acc.account_name as account_name
, 'Cr' as dr_or_cr
, null as amount_dr
, coalesce(v.vat_sale_amount, 0) - coalesce(v.vat_buy_amount, 0) as amount_cr
from fim_vat v
cross join acm_mas_base_account ba
left join acm_mas_account acc on ba.revenue_vat_creditor_account_seq = acc.account_seq
where coalesce(v.vat_sale_amount, 0) - coalesce(v.vat_buy_amount, 0) > 0
and v.vat_seq = @ref_seq
";
                        break;
                    #endregion
                }

                if (strSQL.Length > 0)
                {
                    dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                    if (dtb.Rows.Count > 0)
                    {
                        foreach (DataRow dr in dtb.Rows)
                        {
                            if ((cn.ConvertNullInt(dr["amount_dr"]) != null && cn.ConvertNullInt(dr["amount_dr"]) != 0 && dr["dr_or_cr"].ToString() == "Dr")
                                || (cn.ConvertNullInt(dr["amount_cr"]) != null && cn.ConvertNullInt(dr["amount_cr"]) != 0 && dr["dr_or_cr"].ToString() == "Cr"))
                            {
                                M_ACM_GENERATE_ACCOUNT_Auto info = new M_ACM_GENERATE_ACCOUNT_Auto();
                                info.account_seq = cn.ConvertNullInt(dr["account_seq"]);
                                info.dr_or_cr = dr["dr_or_cr"].ToString();
                                info.account_code = dr["account_code"].ToString();
                                info.account_name = dr["account_name"].ToString();
                                info.amount_dr = cn.ConvertNullDouble(dr["amount_dr"]);
                                info.amount_cr = cn.ConvertNullDouble(dr["amount_cr"]);
                                infoList.Add(info);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }

        public Int32 MaxTransactionGroup()
        {
            DataRowCollection drc = null;
            Int32 tran_group_code = 0;
            try
            {
                dbManager.Open();
                string strSQL = " SELECT COUNT(tran_grp_seq) as Max "
                              + " FROM acm_transection_group ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                if (drc.Count > 0)
                {
                    tran_group_code = Convert.ToInt32(drc[0]["Max"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return tran_group_code;
        }

        public DataTable GetDeliveryNoteInfo(string DnSeq)
        {
            DataTable dtb = null;
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@dn_seq", DnSeq);
            IList<M_ACM_GENERATE_ACCOUNT_Auto> infoList = new List<M_ACM_GENERATE_ACCOUNT_Auto>();
            try
            {
                dbManager.Open();
                string strSQL = "select * from IcmDeliveryNote where DnId = @dn_seq";
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
            return dtb;
        }
    }

    public class M_ACM_GENERATE_ACCOUNT_Auto
    {
        public string ref_type { get; set; }
        public string tran_type { get; set; }
        public string ref_no { get; set; }
        public Nullable<DateTime> tran_date { get; set; }
        public string tran_name { get; set; }
        public Nullable<Int32> work_area_seq { get; set; }
        public Nullable<Int32> bgm_source_seq { get; set; }
        public Nullable<Int32> ref_seq { get; set; }
        public string source_name { get; set; }
        public string area_name { get; set; }
        //Detail
        public Nullable<Int32> account_seq { get; set; }
        public string dr_or_cr { get; set; }
        public Nullable<double> amount { get; set; }
        public Nullable<double> amount_dr { get; set; }
        public Nullable<double> amount_cr { get; set; }
        public Nullable<Int32> tran_grp_seq { get; set; }
        public string tran_grp_code { get; set; }
        public string record_status { get; set; }
        public Int32 created_by { get; set; }
        public DateTime created_when { get; set; }
        public Nullable<Int32> updated_by { get; set; }
        public Nullable<DateTime> updated_when { get; set; }
        public string account_name { get; set; }
        public string account_code { get; set; }
        public Nullable<int> bgm_year { get; set; }
    }
}
